/*
 Navicat Premium Data Transfer

 Source Server         : 本地
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : zhouyi

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 01/06/2024 10:21:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for address_book
-- ----------------------------
DROP TABLE IF EXISTS `address_book`;
CREATE TABLE `address_book` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '地址ID',
  `user_id` int NOT NULL COMMENT '用户ID',
  `consignee` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '收件人姓名',
  `sex` enum('male','female') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '联系电话',
  `province_code` char(6) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '省份编码',
  `province_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '省份名称',
  `city_code` char(6) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '城市编码',
  `city_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '城市名称',
  `district_code` char(6) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区县编码',
  `district_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区县名称',
  `detail` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '详细地址',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除：1-已删除，0-未删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_consignee` (`user_id`,`consignee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户地址簿';

-- ----------------------------
-- Records of address_book
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for categories
-- ----------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `category_id` int NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  `parent_id` int DEFAULT NULL COMMENT '父分类ID',
  `restaurant_id` int DEFAULT NULL COMMENT '餐厅ID',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '分类名称',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分类描述',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '是否启用：1-启用，0-禁用',
  `image_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分类图片URL',
  `sort_order` int NOT NULL DEFAULT '0' COMMENT '排序字段，默认值为0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`category_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`category_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分类信息表';

-- ----------------------------
-- Records of categories
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for coupons
-- ----------------------------
DROP TABLE IF EXISTS `coupons`;
CREATE TABLE `coupons` (
  `coupon_id` int NOT NULL AUTO_INCREMENT COMMENT '优惠券ID',
  `user_id` int NOT NULL COMMENT '用户ID',
  `restaurant_id` int NOT NULL COMMENT '餐厅ID',
  `code` varchar(255) NOT NULL COMMENT '优惠券代码',
  `discount` decimal(10,2) NOT NULL COMMENT '折扣额度',
  `valid_from` timestamp NOT NULL COMMENT '有效期开始时间',
  `valid_until` timestamp NOT NULL COMMENT '有效期结束时间',
  `usage_limit` int NOT NULL COMMENT '使用次数限制',
  `used_status` enum('unused','used','expired') NOT NULL DEFAULT 'unused' COMMENT '使用状态',
  PRIMARY KEY (`coupon_id`),
  UNIQUE KEY `code` (`code`),
  KEY `user_id` (`user_id`),
  KEY `idx_coupon_id` (`coupon_id`),
  KEY `idx_code` (`code`),
  CONSTRAINT `coupons_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='优惠券表';

-- ----------------------------
-- Records of coupons
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for dish_flavors
-- ----------------------------
DROP TABLE IF EXISTS `dish_flavors`;
CREATE TABLE `dish_flavors` (
  `flavor_id` int NOT NULL AUTO_INCREMENT COMMENT '口味ID',
  `restaurant_id` int DEFAULT NULL COMMENT '餐厅ID',
  `flavor_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '口味名称',
  `flavor_value` int NOT NULL COMMENT '口味值',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`flavor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菜品口味表';

-- ----------------------------
-- Records of dish_flavors
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for dishes
-- ----------------------------
DROP TABLE IF EXISTS `dishes`;
CREATE TABLE `dishes` (
  `dish_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '菜品名称',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '菜品描述',
  `price` decimal(10,2) NOT NULL COMMENT '菜品价格',
  `image_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '菜品图片URL',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '菜品是否在售',
  `category_id` int DEFAULT NULL COMMENT '分类ID',
  `dish_flavor_id` int DEFAULT NULL COMMENT '口味ID',
  `restaurant_id` int DEFAULT NULL COMMENT '餐厅ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`dish_id`),
  KEY `category_id` (`category_id`),
  KEY `dish_flavor_id` (`dish_flavor_id`),
  KEY `restaurant_id` (`restaurant_id`),
  CONSTRAINT `dishes_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE,
  CONSTRAINT `dishes_ibfk_2` FOREIGN KEY (`dish_flavor_id`) REFERENCES `dish_flavors` (`flavor_id`) ON DELETE CASCADE,
  CONSTRAINT `dishes_ibfk_3` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`restaurant_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菜品信息表';

-- ----------------------------
-- Records of dishes
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for order_coupon_relations
-- ----------------------------
DROP TABLE IF EXISTS `order_coupon_relations`;
CREATE TABLE `order_coupon_relations` (
  `relation_id` int NOT NULL AUTO_INCREMENT COMMENT '订单优惠券关联ID',
  `order_id` int NOT NULL COMMENT '订单ID',
  `coupon_id` int NOT NULL COMMENT '优惠券ID',
  PRIMARY KEY (`relation_id`),
  UNIQUE KEY `idx_unique_order_coupon` (`order_id`,`coupon_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_coupon_id` (`coupon_id`),
  CONSTRAINT `order_coupon_relations_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE,
  CONSTRAINT `order_coupon_relations_ibfk_2` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`coupon_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单优惠券关联表';

-- ----------------------------
-- Records of order_coupon_relations
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for order_details
-- ----------------------------
DROP TABLE IF EXISTS `order_details`;
CREATE TABLE `order_details` (
  `detail_id` int NOT NULL AUTO_INCREMENT COMMENT '订单详情ID',
  `product_name` varchar(255) NOT NULL COMMENT '商品名称',
  `image` varchar(255) DEFAULT NULL COMMENT '商品图片',
  `order_id` int NOT NULL COMMENT '订单ID',
  `dish_id` int DEFAULT NULL COMMENT '菜品ID',
  `setmeal_id` int DEFAULT NULL COMMENT '套餐ID',
  `flavor_id` int DEFAULT NULL COMMENT '口味id',
  `quantity` int NOT NULL COMMENT '数量',
  `amount` decimal(10,2) NOT NULL COMMENT '商品原价',
  `discounted_price` decimal(10,2) NOT NULL COMMENT '打折后价格',
  PRIMARY KEY (`detail_id`),
  KEY `flavor_id` (`flavor_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_dish_id` (`dish_id`),
  KEY `idx_setmeal_id` (`setmeal_id`),
  CONSTRAINT `order_details_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE,
  CONSTRAINT `order_details_ibfk_2` FOREIGN KEY (`dish_id`) REFERENCES `dishes` (`dish_id`) ON DELETE SET NULL,
  CONSTRAINT `order_details_ibfk_3` FOREIGN KEY (`setmeal_id`) REFERENCES `setmeals` (`setmeal_id`) ON DELETE SET NULL,
  CONSTRAINT `order_details_ibfk_4` FOREIGN KEY (`flavor_id`) REFERENCES `dish_flavors` (`flavor_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单详情表';

-- ----------------------------
-- Records of order_details
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `status_description` enum('待付款','待接单','已接单','派送中','已完成','已取消') NOT NULL COMMENT '订单状态',
  `user_id` int NOT NULL COMMENT '用户ID',
  `delivery_address` varchar(255) NOT NULL COMMENT '配送地址',
  `order_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `checkout_time` timestamp NULL DEFAULT NULL COMMENT '支付完成时间',
  `pay_method` enum('credit_card','alipay','wechat_pay') DEFAULT NULL COMMENT '支付方式',
  `pay_status` enum('unpaid','paid') DEFAULT NULL COMMENT '支付状态',
  `amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `remark` text COMMENT '订单备注',
  `delivery_time` datetime DEFAULT NULL COMMENT '预计送达时间',
  PRIMARY KEY (`order_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status_description` (`status_description`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

-- ----------------------------
-- Records of orders
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for promotions
-- ----------------------------
DROP TABLE IF EXISTS `promotions`;
CREATE TABLE `promotions` (
  `promotion_id` int NOT NULL AUTO_INCREMENT COMMENT '促销活动ID',
  `name` varchar(255) NOT NULL COMMENT '促销活动名称',
  `description` text COMMENT '促销活动描述',
  `start_date` datetime NOT NULL COMMENT '开始日期和时间',
  `end_date` datetime NOT NULL COMMENT '结束日期和时间',
  `status` enum('active','inactive','suspended') NOT NULL COMMENT '活动状态',
  `discount_type` enum('percentage','fixed_amount','buy_x_get_y_free') NOT NULL COMMENT '折扣类型',
  `discount_value` decimal(10,2) NOT NULL COMMENT '折扣值',
  `eligibility` text COMMENT '资格条件',
  `applies_to` text COMMENT '适用范围',
  `limitations` text COMMENT '限制条件',
  `restaurant_id` int NOT NULL COMMENT '餐厅ID',
  `created_by` int DEFAULT NULL COMMENT '创建者ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`promotion_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_restaurant_id` (`restaurant_id`),
  CONSTRAINT `promotions_ibfk_1` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`restaurant_id`) ON DELETE RESTRICT,
  CONSTRAINT `promotions_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `user` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='促销活动表';

-- ----------------------------
-- Records of promotions
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for restaurants
-- ----------------------------
DROP TABLE IF EXISTS `restaurants`;
CREATE TABLE `restaurants` (
  `restaurant_id` int NOT NULL AUTO_INCREMENT COMMENT '餐厅ID',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '餐厅名称',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '餐厅描述',
  `latitude` decimal(11,8) NOT NULL COMMENT '纬度',
  `longitude` decimal(11,8) NOT NULL COMMENT '经度',
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '餐厅地址',
  `operating_hours` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '营业时间',
  `rating` decimal(2,1) DEFAULT NULL COMMENT '餐厅评分',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`restaurant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='餐厅信息表';

-- ----------------------------
-- Records of restaurants
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for reviews
-- ----------------------------
DROP TABLE IF EXISTS `reviews`;
CREATE TABLE `reviews` (
  `review_id` int NOT NULL AUTO_INCREMENT COMMENT '评论ID',
  `user_id` int NOT NULL COMMENT '用户ID',
  `restaurant_id` int NOT NULL COMMENT '餐厅ID',
  `review_content` text NOT NULL COMMENT '评论内容',
  `rating` tinyint NOT NULL COMMENT '评分',
  `review_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
  PRIMARY KEY (`review_id`),
  KEY `idx_review_id` (`review_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_restaurant_id` (`restaurant_id`),
  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`restaurant_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='评论表';

-- ----------------------------
-- Records of reviews
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for setmeal_dishes
-- ----------------------------
DROP TABLE IF EXISTS `setmeal_dishes`;
CREATE TABLE `setmeal_dishes` (
  `setmeal_id` int NOT NULL COMMENT '套餐ID',
  `dish_id` int NOT NULL COMMENT '菜品ID',
  PRIMARY KEY (`setmeal_id`,`dish_id`),
  KEY `dish_id` (`dish_id`),
  CONSTRAINT `setmeal_dishes_ibfk_1` FOREIGN KEY (`setmeal_id`) REFERENCES `setmeals` (`setmeal_id`) ON DELETE CASCADE,
  CONSTRAINT `setmeal_dishes_ibfk_2` FOREIGN KEY (`dish_id`) REFERENCES `dishes` (`dish_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='套餐与菜品关系表';

-- ----------------------------
-- Records of setmeal_dishes
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for setmeals
-- ----------------------------
DROP TABLE IF EXISTS `setmeals`;
CREATE TABLE `setmeals` (
  `setmeal_id` int NOT NULL AUTO_INCREMENT COMMENT '套餐ID',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '套餐名称',
  `price` decimal(10,2) NOT NULL COMMENT '套餐价格',
  `is_active` tinyint(1) DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `category_id` int DEFAULT NULL COMMENT '分类ID',
  `restaurant_id` int DEFAULT NULL COMMENT '餐厅ID',
  `created_by` int DEFAULT NULL COMMENT '创建者ID',
  `updated_by` int DEFAULT NULL COMMENT '更新者ID',
  `create_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`setmeal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='套餐信息表';

-- ----------------------------
-- Records of setmeals
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sku_audit_logs
-- ----------------------------
DROP TABLE IF EXISTS `sku_audit_logs`;
CREATE TABLE `sku_audit_logs` (
  `audit_id` bigint NOT NULL AUTO_INCREMENT COMMENT '审计记录ID',
  `sku_id` bigint NOT NULL COMMENT '关联的SKU ID',
  `action` enum('create','update','delete') NOT NULL COMMENT '操作类型',
  `changed_fields` json DEFAULT NULL COMMENT '变更的字段及前后值（JSON格式）',
  `user_id` bigint DEFAULT NULL COMMENT '执行操作的用户ID',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  PRIMARY KEY (`audit_id`),
  KEY `idx_sku_id` (`sku_id`),
  KEY `idx_timestamp` (`timestamp`),
  CONSTRAINT `sku_audit_logs_ibfk_1` FOREIGN KEY (`sku_id`) REFERENCES `skus` (`sku_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='SKU审计日志表';

-- ----------------------------
-- Records of sku_audit_logs
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for skus
-- ----------------------------
DROP TABLE IF EXISTS `skus`;
CREATE TABLE `skus` (
  `sku_id` bigint NOT NULL AUTO_INCREMENT COMMENT 'SKU ID',
  `dish_id` int NOT NULL COMMENT '关联的菜品ID',
  `flavor` varchar(255) DEFAULT NULL COMMENT '口味',
  `specification` json DEFAULT NULL COMMENT '规格（JSON格式）',
  `stock_quantity` int NOT NULL DEFAULT '0' COMMENT '库存数量',
  `stock_threshold` int NOT NULL DEFAULT '0' COMMENT '库存预警阈值',
  `price` decimal(10,2) NOT NULL COMMENT '售价',
  `cost` decimal(10,2) DEFAULT NULL COMMENT '成本价',
  `image_url` varchar(255) DEFAULT NULL COMMENT 'SKU图片URL（如有不同）',
  `status` enum('active','inactive','pending','discontinued') NOT NULL DEFAULT 'active' COMMENT 'SKU状态',
  `approval_status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending' COMMENT '审批状态',
  `promotion_info` json DEFAULT NULL COMMENT '促销活动信息（JSON格式）',
  `version` int NOT NULL DEFAULT '1' COMMENT '版本号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `supplier_id` bigint DEFAULT NULL COMMENT '供应商ID',
  PRIMARY KEY (`sku_id`),
  KEY `idx_sku_id` (`sku_id`),
  KEY `idx_dish_id` (`dish_id`),
  KEY `idx_supplier_id` (`supplier_id`),
  CONSTRAINT `skus_ibfk_1` FOREIGN KEY (`dish_id`) REFERENCES `dishes` (`dish_id`) ON DELETE CASCADE,
  CONSTRAINT `skus_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='SKU表';

-- ----------------------------
-- Records of skus
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for suppliers
-- ----------------------------
DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
  `supplier_id` bigint NOT NULL AUTO_INCREMENT COMMENT '供应商ID',
  `name` varchar(255) NOT NULL COMMENT '供应商名称',
  `contact_name` varchar(255) DEFAULT NULL COMMENT '联系人姓名',
  `email` varchar(255) DEFAULT NULL COMMENT '供应商电子邮件',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `address` varchar(255) DEFAULT NULL COMMENT '供应商地址',
  `city` varchar(100) DEFAULT NULL COMMENT '城市',
  `state` varchar(100) DEFAULT NULL COMMENT '州/省',
  `postal_code` varchar(20) DEFAULT NULL COMMENT '邮政编码',
  `country` varchar(100) DEFAULT NULL COMMENT '国家',
  `credit_limit` decimal(10,2) DEFAULT NULL COMMENT '信用额度',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT '供应商状态',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`supplier_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='供应商表';

-- ----------------------------
-- Records of suppliers
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `setmeal_id` int NOT NULL COMMENT '套餐ID',
  `openid` varchar(255) NOT NULL COMMENT '开放ID',
  `name` varchar(100) NOT NULL COMMENT '用户名',
  `phone` varchar(11) NOT NULL COMMENT '电话号码',
  `sex` enum('male','female') DEFAULT NULL COMMENT '性别',
  `identification` varchar(18) NOT NULL COMMENT '身份证号码',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像URL',
  `email` varchar(100) DEFAULT NULL COMMENT '电子邮件',
  `email_verified` tinyint(1) NOT NULL DEFAULT '0' COMMENT '电子邮件验证状态',
  `username` varchar(50) DEFAULT NULL COMMENT '登录用户名',
  `password_hash` varchar(255) NOT NULL COMMENT '密码哈希值',
  `province` char(20) DEFAULT NULL COMMENT '省份',
  `city` char(20) DEFAULT NULL COMMENT '城市',
  `district` char(20) DEFAULT NULL COMMENT '区县',
  `detail_address` varchar(100) DEFAULT NULL COMMENT '详细地址',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `openid` (`openid`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_username` (`username`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
